/*
 * 
 *
 * Created on March 22, 2007, 12:34 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.samlam;
import java.util.*;
import java.io.*;
import java.sql.*;

/**
 * PhysicalDAO
 * @author Brian Barrett
 */
public class PhysicalDAO extends ProductDAO 
{
    
    
    /** Creates a new instance of PhysicalDAO */
    protected PhysicalDAO() {
        super();
    }
    /**
     * instantiate instance
     */
    protected static PhysicalDAO instance = null;
        
    /**
     * get instance
     * @return get instance
     */
    public static synchronized PhysicalDAO getInstance()
    {
        if (instance == null) {
            instance = new PhysicalDAO();
        }
        return instance;
    }
    
    /**
     * create
     * @return create
     */
    public Physical create()
    {
        Physical physical = null;
        try {
            String id = GUID.generate();
            physical = new Physical(id);
            Cache p = Cache.getInstance();
            p.put(physical.getId(),physical);
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return physical;
    }
    
    /**
     * read
     * @param id id
     * @throws edu.byu.isys413.samlam.DataException exception
     * @throws edu.byu.isys413.samlam.ConnectionPoolException exception
     * @return read
     */
    public Physical read(String id) throws DataException,ConnectionPoolException
    {
        
        try
        {
            Connection conn = ConnectionPool.getInstance().get();
            Physical conceptual= read(id,conn);
            ConnectionPool.getInstance().release(conn);
            return conceptual;
        }
        catch (SQLException e)
        {
            // rollback
            throw new DataException("SQL error from read 1", e);
        }
        catch (ConnectionPoolException e)
        {
            throw new ConnectionPoolException("Connection pool error in read 1", e);
        }
    }
    
    /**
     * read
     * @param id id
     * @param conn conn
     * @throws java.sql.SQLException exception
     * @throws edu.byu.isys413.samlam.DataException exception
     * @throws edu.byu.isys413.samlam.ConnectionPoolException excpetion
     * @return read
     */
    public Physical read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException{
        Physical physical = (Physical)Cache.getInstance().get(id);
        if(Cache.getInstance().containsKey(id)){
            
            return physical;
            } 
                
           try{        
           PreparedStatement stmt = conn.prepareStatement("SELECT * FROM conceptual WHERE conceptualid=?");
           stmt.setString(1,id);
           ResultSet rs =  stmt.executeQuery();
           
           ConnectionPool.getInstance().release(conn);
            
            if (rs.next()) 
            {
                //Physical physical = (Physical)PhysicalDAO.getInstance().create();
               physical = new Physical(id);
               Cache.getInstance().put(id,physical);
                super.read(id);
                
                physical.setDatePurchased(rs.getString("datepurchased"));
                if(rs.getInt("saleorrent")==0){
                    physical.setSaleorRent(false);
                }else{
                    physical.setSaleorRent(true);
                }
                if(rs.getInt("neworused")==0){
                    physical.setNewOrUsed(false);
                }else{
                    physical.setNewOrUsed(true);
                }
                physical.setSerialNumber(rs.getString("serialnumber"));
                physical.setShelflocation(rs.getString("shelflocation"));
                
                
                physical.setConceptual(ConceptualDAO.getInstance().read(rs.getString("conceptualid"), conn));
                physical.setStore(StoreDAO.getInstance().read(rs.getString("storeid"), conn));
                
                
                
                Cache.getInstance().put(id,physical);      
                physical.setObjectAlreadyInDB(true);
                stmt.close();
                return physical;
               

            }
            else
            {
                throw new DataException("Product was not found in the database.");
            }
           
            }
           catch (ConnectionPoolException e)
           {
                throw new ConnectionPoolException("Could not retrieve a connection from the database", e);
            
            }
     catch (SQLException e){
                throw new DataException("SQL error", e);
            }
    }
    
    /**
     * save
     * @param prod physical
     * @throws edu.byu.isys413.samlam.DataException exception
     * @throws edu.byu.isys413.samlam.ConnectionPoolException exception
     */
     public synchronized void save(Physical prod) throws DataException,ConnectionPoolException 
     {
           
        try
        {
            Connection conn = ConnectionPool.getInstance().get();
            save(prod,conn);
            ConnectionPool.getInstance().release(conn);
        }
        catch (SQLException e)
        {
            //rollback
            e.printStackTrace();
            //throw new DataException("Could not retrieve record for id=" + prod.getId(), e);
            
         }
        catch (ConnectionPoolException e)
        {
            //throw new ConnectionPoolException("Could not retrieve record for id=" + e);
                e.printStackTrace();
        }
        
        // release the connection back to the pool
        return;
    }
     synchronized void save(Physical prod, Connection conn) throws SQLException, DataException, ConnectionPoolException 
     {
        // check the dirty flag in the RevenueSourceDBO.  if it is dirty, 
        // run update or insert
        
        if (prod.isDirty()) 
        {
       
             if (prod.isObjectAlreadyInDB()) 
            {
               System.out.println("in the update ");
                update((Physical) prod, conn);
            }
            else
            {
               System.out.println("Entering insert of conceptual");
               insert(prod, conn);
               prod.setObjectAlreadyInDB(true);
            }
            
        Cache.getInstance().touch(prod.getId());
     
        }
        
    }

    private synchronized void update(Physical prod, Connection conn) throws SQLException, DataException, ConnectionPoolException
    {
        PreparedStatement stmt = conn.prepareStatement("UPDATE pysical SET physicalid=?,serialnumber=?,shelflocation=?,datepurchased=?, forsale=?, isnew=?, conceptualid=?, storeid=? WHERE physicalid=? ");
        
        stmt.setString(1,prod.getId());
        stmt.setString(2,prod.getSerialNumber());
        stmt.setString(3, prod.getShelflocation());        
        stmt.setString(4, prod.getDatePurchased());
        if(prod.isNewOrUsed() == true){
            stmt.setInt(5, 1);
        }else{
            stmt.setInt(5, 0);
        }
        if(prod.isSaleorRent() == true){
            stmt.setInt(6, 1);
        }else{
            stmt.setInt(6, 0);
        }
        stmt.setString(7, prod.getConceptual().getId());
        stmt.setString(8, prod.getStore().getId());
        stmt.setString(9, prod.getId());
        stmt.execute();
        stmt.close();
        super.save((Product)prod,conn);
        ConnectionPool.getInstance().release(conn);
        
    }
    private synchronized void insert(Physical prod, Connection conn) throws SQLException, DataException, ConnectionPoolException
    {
      
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO physical VALUES ( ?,?,?,?,?,?,?,?)");
        
        Physical physical = (Physical)prod;
        stmt.setString(1,prod.getId());
        stmt.setString(2,prod.getSerialNumber());
        stmt.setString(3, prod.getShelflocation());        
        stmt.setString(4, prod.getDatePurchased());
        if(prod.isNewOrUsed() == true){
            stmt.setInt(5, 1);
        }else{
            stmt.setInt(5, 0);
        }
        if(prod.isSaleorRent() == true){
            stmt.setInt(6, 1);
        }else{
            stmt.setInt(6, 0);
        }
        stmt.setString(7, prod.getConceptual().getId());
        stmt.setString(8, prod.getStore().getId());
        
        stmt.execute();
        super.save((Product)prod,conn);
        prod.setObjectAlreadyInDB(true);
        physical.setObjectAlreadyInDB(true);
        stmt.close();
        // tell the RevenueSourceDBO that it's now in the db (so we call update next time not insert)
        
        ConnectionPool.getInstance().release(conn);
    }
    
    

    /**
     * read by category
     * @param category category
     * @return read by category
     */
    public List<Physical> readByCategory(String category) {
         List<Physical> conceptualList = new LinkedList<Physical>();
        // get the connection
        
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            
            // sql the customer using phoneNumber
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT conceptualid FROM conceptual WHERE category ='"+category+"'");
            
            // while loop to populate the list from the results
            while(rs.next()) {
                String conceptualid = rs.getString("conceptualid");
                Physical p = this.read(conceptualid, conn);
                conceptualList.add(p);
            }
            // release my connection
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        } catch (DataException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ConnectionPoolException ex) {
            ex.printStackTrace();
        }
            // return the list
            return conceptualList;
    }
}
